package cn.batch.daemon.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import cn.batch.daemon.bean.ApplicantCmInfo;
import cn.batch.daemon.bean.ApplicantInfo;
import cn.batch.daemon.bean.ApplicantLoanInfo;
import cn.batch.daemon.bean.ApplicantSpouseInfo;
import cn.batch.daemon.bean.GuarantorInfo;
import cn.batch.daemon.bean.IN_PreApproval;
import cn.batch.daemon.hibernate.bean.BatchRiskData;
import cn.batch.daemon.procWebservice.bean.HtmlParseRequest;
import cn.batch.daemon.util.ChangeCertType;
import cn.batch.daemon.util.JdbcUtils;

import com.skyon.core.config.BaseJNDIEnv;
import com.skyon.core.jdbc.ConnectionProvider;

/**
 * 风险预警客户数据操作类 风险预警批处理信息表--I_INP_BATCH_RISK
 * 
 * @author xihn
 * 
 */
public class RiskWarningStore {
	private static Log logger = LogFactory.getLog(RiskWarningStore.class);

	private static final String BANKCODE = "0120";

	private static ConnectionProvider connectionProvider;

	// 风险预警客户信息按天
	private static String RISK_INFO_DAY_SQL = " SELECT LOANACNO,CUSTID,PRO_TYPE,PAPERID,PAPERKIND,MAX_CYCLE_DAY_ASS,MAX_CYCLE_DAY_CONS,BK_IN_FLAG,BK_OT_FLAG,"
			+ "CON_BK_IN_FLAG,CON_BK_OT_FLAG,ASS_BK_IN_FLAG,ASS_BK_OT_FLAG FROM I_INP_BATCH_RISK WHERE RECORD_DATE = to_date(?, 'yyyy-MM-dd')  ORDER BY CUSTID ASC ";

	// 风险预警客户信息按月
	private static String RISK_INFO_MONTH_SQL = "SELECT LOANACNO,CUSTID,PAPERID,PAPERKIND,EXCEP_FLAG,"
			+ "CYCLE_ACCT_RATE,CYCLE_ACCT_NUM,CYCLE_CUST_RATE_6,CYCLE_ACCT_NUM_6,CRED_AMT_SUM,CON_CRED_AMT_SUM,"
			+ "LOAN_BAL_SUM,CON_LOAN_BAL_SUM FROM BATCH_RISK_DATA WHERE PROC_FLAG = '1' AND BATCH_NO = ?  ORDER BY CUSTID ASC ";//  修改日期：2013-7-26  hlRain PROC_FLAG 改为1
	// oracle 分页语句格式
	private static String PAGE_FORMAT_SQL = " SELECT * FROM ( SELECT q.*, rownum as rowno FROM ( QS ) q where rownum <= ? ) t WHERE t.rowno > ? ";

	public static void init() {
		BaseJNDIEnv.init();
		connectionProvider = new JdbcConnectionOracleProvider();
	}
	
	public static int getRiskCustInfoCount(String qDate) throws SQLException {
		init();
		Connection connection = null;
		PreparedStatement preStatement = null;
		ResultSet rs = null;
		int count = 0;
		String sql = " select count(1) from I_INP_BATCH_RISK where RECORD_DATE = to_date(?, 'yyyy-MM-dd') ";
		connection = connectionProvider.getConnection();
		preStatement = connection.prepareStatement(sql);
		preStatement.setString(1, qDate);
		rs = preStatement.executeQuery();
		while (rs.next()) {
			count = rs.getInt(1);
		}
		return count;
	}
	
	public static List<IN_PreApproval> getRiskCustInfoPage(String qDate,
			int fromRow, int toRow) throws SQLException {
		init();
		Connection connection = null;
		PreparedStatement preStatement = null;
		ResultSet rs = null;
		List<IN_PreApproval> l = new ArrayList<IN_PreApproval>();
		String sql = PAGE_FORMAT_SQL.replace("QS", RISK_INFO_DAY_SQL);
		try {
			connection = connectionProvider.getConnection();
			preStatement = connection.prepareStatement(sql);
			preStatement.setString(1, qDate);
			preStatement.setInt(2, toRow);
			preStatement.setInt(3, fromRow);
			rs = preStatement.executeQuery();

			while (rs.next()) {
				IN_PreApproval in = new IN_PreApproval();
				/* 申请人信息 */
				ApplicantInfo ali = new ApplicantInfo();
				/* 申请人配偶信息 */
				ApplicantSpouseInfo alsi = new ApplicantSpouseInfo();
				/* 担保人信息 */
				GuarantorInfo gi = new GuarantorInfo();
				/* 申请人贷款信息 */
				ApplicantLoanInfo alli = new ApplicantLoanInfo();

				// 贷款账号
				if (rs.getString(1) != null) {
					alli.setLoanAccount(rs.getString(1));
				}
				// 客户编号
				if (rs.getString(2) != null) {
					ali.setCustNumber(rs.getString(2));
				}
				// 产品类型
				if (rs.getString(3) != null) {
					alli.setLoanCategory(rs.getString(3));
				}
				// 证件号码
				if (rs.getString(4) != null) {
					ali.setIdNumber(rs.getString(4));
				}
				// 证件类型
				if (rs.getString(5) != null) {
					ali.setIdType(rs.getString(5));
				}
				// 担保人本行贷款最大逾期天数
				if (rs.getString(6) != null) {
					gi.setGrarantorLoanOt(rs.getBigDecimal(6));
				}
				// 配偶本行贷款最大逾期天数
				if (rs.getString(7) != null) {
					alsi.setSpoLoanOt(rs.getBigDecimal(7));
				}
				// 是否行内黑名单
				if (rs.getString(8) != null) {
					ali.setAppBankBcFlag(rs.getString(8));
				}
				// 是否行外黑名单
				if (rs.getString(9) != null) {
					ali.setAppCbrcBcFlag(rs.getString(9));
				}
				// 配偶是否行内黑名单
				if (rs.getString(10) != null) {
					alsi.setSpoBankBcFlag(rs.getString(10));
				}
				// 配偶是否行外黑名单
				if (rs.getString(11) != null) {
					alsi.setSpoCbrcBcFlag(rs.getString(11));
				}
				// 担保人是否行内黑名单
				if (rs.getString(12) != null) {
					gi.setGuaBankBcFlag(rs.getString(12));
				}
				// 担保人是否行外黑名单
				if (rs.getString(13) != null) {
					gi.setGuaCbrcBcFlag(rs.getString(13));
				}
				
				in.setApplicantInfo(ali);
				in.setApplicantSpouseInfo(alsi);
				in.setGuarantorInfo(gi);
				in.setApplicantLoanInfo(alli);
				l.add(in);
			}

		} catch (SQLException e) {
			logger.error("查询风险预警待处理客户数据出错 QUERY SQL:" + sql, e);
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(preStatement);
			JdbcUtils.closeConnection(connection);
		}
		return l;
	}
	
	/**
	 * 风险预警批量处理--获得待处理的客户数据
	 * 
	 * @return List
	 * @throws SQLException
	 * @throws Exception
	 */
	public static List<IN_PreApproval> getRiskCustInfo(String flagDate)
			throws SQLException {

		init();
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		List<IN_PreApproval> l = new ArrayList<IN_PreApproval>();
		String sql = RISK_INFO_DAY_SQL + flagDate + "'";
		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);

			while (rs.next()) {
				IN_PreApproval in = new IN_PreApproval();
				/* 申请人信息 */
				ApplicantInfo ali = new ApplicantInfo();
				/* 申请人配偶信息 */
				ApplicantSpouseInfo alsi = new ApplicantSpouseInfo();
				/* 担保人信息 */
				GuarantorInfo gi = new GuarantorInfo();
				/* 申请人贷款信息 */
				ApplicantLoanInfo alli = new ApplicantLoanInfo();

				// 贷款账号
				if (rs.getString(1) != null) {
					alli.setLoanAccount(rs.getString(1));
				}
				// 客户编号
				if (rs.getString(2) != null) {
					ali.setCustNumber(rs.getString(2));
				}
				// 产品类型
				if (rs.getString(3) != null) {
					alli.setLoanCategory(rs.getString(3));
				}
				// 证件号码
				if (rs.getString(4) != null) {
					ali.setIdNumber(rs.getString(4));
				}
				// 证件类型
				if (rs.getString(5) != null) {
					ali.setIdType(rs.getString(5));
				}
				// 担保人本行贷款最大逾期天数
				if (rs.getString(6) != null) {
					gi.setGrarantorLoanOt(rs.getBigDecimal(6));
				}
				// 配偶本行贷款最大逾期天数
				if (rs.getString(7) != null) {
					alsi.setSpoLoanOt(rs.getBigDecimal(7));
				}
				// 是否行内黑名单
				if (rs.getString(8) != null) {
					ali.setAppBankBcFlag(rs.getString(8));
				}
				// 是否行外黑名单
				if (rs.getString(9) != null) {
					ali.setAppCbrcBcFlag(rs.getString(9));
				}
				// 配偶是否行内黑名单
				if (rs.getString(10) != null) {
					alsi.setSpoBankBcFlag(rs.getString(10));
				}
				// 配偶是否行外黑名单
				if (rs.getString(11) != null) {
					alsi.setSpoCbrcBcFlag(rs.getString(11));
				}
				// 担保人是否行内黑名单
				if (rs.getString(12) != null) {
					gi.setGuaBankBcFlag(rs.getString(12));
				}
				// 担保人是否行外黑名单
				if (rs.getString(13) != null) {
					gi.setGuaCbrcBcFlag(rs.getString(13));
				}
				in.setApplicantInfo(ali);
				in.setApplicantSpouseInfo(alsi);
				in.setGuarantorInfo(gi);
				in.setApplicantLoanInfo(alli);
				l.add(in);
			}

		} catch (SQLException e) {
			logger.error("查询风险预警待处理客户数据出错 QUERY SQL:" + sql, e);
			e.printStackTrace();
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
		return l;
	}
	
	public static int getRiskCustInfoMonthCount(String flagDate) throws SQLException {
		init();
		Connection connection = null;
		PreparedStatement preStatement = null;
		ResultSet rs = null;
		int count = 0;
		String sql = " select count(1) from BATCH_RISK_DATA where BATCH_NO = ? and PROC_FLAG = '1'  ";//";   日期：2013-7-26 hlRain PROC_FLAG = '1'状态为1
		connection = connectionProvider.getConnection();
		preStatement = connection.prepareStatement(sql);
		preStatement.setString(1, flagDate);
		rs = preStatement.executeQuery();
		while (rs.next()) {
			count = rs.getInt(1);
		}
		return count;
	}
	
	public static List<IN_PreApproval> getRiskCustInfoMonthPage(
			String flagDate, int fromRow, int toRow) throws SQLException {
		init();
		Connection connection = null;
		PreparedStatement preStatement = null;
		ResultSet rs = null;
		List<IN_PreApproval> l = new ArrayList<IN_PreApproval>();
		String sql = PAGE_FORMAT_SQL.replace("QS", RISK_INFO_MONTH_SQL);
		try {
			connection = connectionProvider.getConnection();
			preStatement = connection.prepareStatement(sql);
			preStatement.setString(1, flagDate);
			preStatement.setInt(2, toRow);
			preStatement.setInt(3, fromRow);
			rs = preStatement.executeQuery();

			while (rs.next()) {
				IN_PreApproval in = new IN_PreApproval();
				/* 申请人信息 */
				ApplicantInfo ali = new ApplicantInfo();
				/* 申请人配偶信息 */
				ApplicantSpouseInfo alsi = new ApplicantSpouseInfo();
				/* 申请人贷款信息 */
				ApplicantLoanInfo alli = new ApplicantLoanInfo();
				/* 申请人征信信息 */
//				ApplicantPbocInfo alpi = new ApplicantPbocInfo();
				/* 申请人所属客户经理信息 */
				ApplicantCmInfo alci = new ApplicantCmInfo();
				/* 申请人征信贷记卡明细 */
//				ApplicantPbocCcInfo alpci = new ApplicantPbocCcInfo();
				/* 申请人征信贷款明细 */
//				ApplicantPbocLoanInfo alpli = new ApplicantPbocLoanInfo();

				// 贷款账号
				if (rs.getString("LOANACNO") != null) {
					alli.setLoanAccount(rs.getString("LOANACNO").trim());
				}
				// 客户编号
				if (rs.getString("CUSTID") != null) {
					ali.setCustNumber(rs.getString("CUSTID").trim());
				}
				// 证件号码
				if (rs.getString("PAPERID") != null) {
					ali.setIdNumber(rs.getString("PAPERID").trim());
				}
				// 证件类型
				if (rs.getString("PAPERKIND") != null) {
					ali.setIdType(rs.getString("PAPERKIND").trim());
				}
				// 是否例外授信
				if (rs.getString("EXCEP_FLAG") != null) {
					alli.setExceptionCredit(rs.getString("EXCEP_FLAG").trim());
				}
				// 同一客户经理下同月处于逾期状态账户比率
				if (rs.getString("CYCLE_ACCT_RATE") != null) {
					alci.setOverdueAccountRateM1(rs
							.getBigDecimal("CYCLE_ACCT_RATE"));
				}
				// 同一客户经理下同月逾期笔数
				if (rs.getString("CYCLE_ACCT_NUM") != null) {
					alci.setOverdueCountM1(rs.getBigDecimal("CYCLE_ACCT_NUM"));
				}
				// 同一客户经理下过去6个月逾期客户比例
				if (rs.getString("CYCLE_CUST_RATE_6") != null) {
					alci.setOverdueCustomerRateM6(rs
							.getBigDecimal("CYCLE_CUST_RATE_6"));
				}
				// 同一客户经理下过去6个月逾期笔数
				if (rs.getString("CYCLE_ACCT_NUM_6") != null) {
					alci.setOverdueCountM6(rs.getBigDecimal("CYCLE_ACCT_NUM_6"));
				}
				// 申请人同产品授信总额
				if (rs.getString("CRED_AMT_SUM") != null) {
					alli.setAppTotalCa(rs.getBigDecimal("CRED_AMT_SUM"));
				}
				// 申请人配偶同产品授信总额
				if (rs.getString("CON_CRED_AMT_SUM") != null) {
					alsi.setSpoTotalCa(rs.getBigDecimal("CON_CRED_AMT_SUM"));
				}
				// 申请人行内贷款余额
				if (rs.getString("LOAN_BAL_SUM") != null) {
					alli.setAppLoanBalance(rs.getBigDecimal("LOAN_BAL_SUM"));
				}
				// 申请人配偶行内贷款余额
				if (rs.getString("CON_LOAN_BAL_SUM") != null) {
					alsi.setSpoLoanBalance(rs.getBigDecimal("CON_LOAN_BAL_SUM"));
				}

				in.setApplicantInfo(ali);
				in.setApplicantSpouseInfo(alsi);
				in.setApplicantLoanInfo(alli);
				in.setApplicantCmInfo(alci);
//				in.setApplicantPbocInfo(alpi);
//				in.getApplicantPbocInfo().getApplicantPbocLoanInfos()
//						.add(alpli);
//				in.getApplicantPbocInfo().getApplicantPbocCcInfos().add(alpci);
				l.add(in);
			}

		} catch (SQLException e) {
			logger.error("查询风险预警待处理客户数据出错 QUERY SQL:" + sql, e);
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(preStatement);
			JdbcUtils.closeConnection(connection);
		}
		return l;
	}

	/**
	 * 风险预警(按月)批量处理--获得待处理的客户数据
	 * 
	 * @return List
	 * @throws SQLException
	 * @throws ParseException
	 * @throws Exception
	 */
	public static List<IN_PreApproval> getRiskCustInfoMonth(String flagDate) throws SQLException,
			ParseException {
		init();
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		List<IN_PreApproval> l = new ArrayList<IN_PreApproval>();
		String sql = RISK_INFO_MONTH_SQL + flagDate + "' and PROC_FLAG = '0' ";
		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);

			while (rs.next()) {
				IN_PreApproval in = new IN_PreApproval();
				/* 申请人信息 */
				ApplicantInfo ali = new ApplicantInfo();
				/* 申请人配偶信息 */
				ApplicantSpouseInfo alsi = new ApplicantSpouseInfo();
				/* 申请人贷款信息 */
				ApplicantLoanInfo alli = new ApplicantLoanInfo();
				/* 申请人征信信息 */
//				ApplicantPbocInfo alpi = new ApplicantPbocInfo();
				/* 申请人所属客户经理信息 */
				ApplicantCmInfo alci = new ApplicantCmInfo();
				/* 申请人征信贷记卡明细 */
//				ApplicantPbocCcInfo alpci = new ApplicantPbocCcInfo();
				/* 申请人征信贷款明细 */
//				ApplicantPbocLoanInfo alpli = new ApplicantPbocLoanInfo();

				// 贷款账号
				if (rs.getString("LOANACNO") != null) {
					alli.setLoanAccount(rs.getString("LOANACNO").trim());
				}
				// 客户编号
				if (rs.getString("CUSTID") != null) {
					ali.setCustNumber(rs.getString("CUSTID").trim());
				}
				// 证件号码
				if (rs.getString("PAPERID") != null) {
					ali.setIdNumber(rs.getString("PAPERID").trim());
				}
				// 证件类型
				if (rs.getString("PAPERKIND") != null) {
					ali.setIdType(rs.getString("PAPERKIND").trim());
				}
				// 是否例外授信
				if (rs.getString("EXCEP_FLAG") != null) {
					alli.setExceptionCredit(rs.getString("EXCEP_FLAG").trim());
				}
				// 同一客户经理下同月处于逾期状态账户比率
				if (rs.getString("CYCLE_ACCT_RATE") != null) {
					alci.setOverdueAccountRateM1(rs
							.getBigDecimal("CYCLE_ACCT_RATE"));
				}
				// 同一客户经理下同月逾期笔数
				if (rs.getString("CYCLE_ACCT_NUM") != null) {
					alci.setOverdueCountM1(rs.getBigDecimal("CYCLE_ACCT_NUM"));
				}
				// 同一客户经理下过去6个月逾期客户比例
				if (rs.getString("CYCLE_CUST_RATE_6") != null) {
					alci.setOverdueCustomerRateM6(rs
							.getBigDecimal("CYCLE_CUST_RATE_6"));
				}
				// 同一客户经理下过去6个月逾期笔数
				if (rs.getString("CYCLE_ACCT_NUM_6") != null) {
					alci.setOverdueCountM6(rs.getBigDecimal("CYCLE_ACCT_NUM_6"));
				}
				// 申请人同产品授信总额
				if (rs.getString("CRED_AMT_SUM") != null) {
					alli.setAppTotalCa(rs.getBigDecimal("CRED_AMT_SUM"));
				}
				// 申请人配偶同产品授信总额
				if (rs.getString("CON_CRED_AMT_SUM") != null) {
					alsi.setSpoTotalCa(rs.getBigDecimal("CON_CRED_AMT_SUM"));
				}
				// 申请人行内贷款余额
				if (rs.getString("LOAN_BAL_SUM") != null) {
					alli.setAppLoanBalance(rs.getBigDecimal("LOAN_BAL_SUM"));
				}
				// 申请人配偶行内贷款余额
				if (rs.getString("CON_LOAN_BAL_SUM") != null) {
					alsi.setSpoLoanBalance(rs.getBigDecimal("CON_LOAN_BAL_SUM"));
				}

				in.setApplicantInfo(ali);
				in.setApplicantSpouseInfo(alsi);
				in.setApplicantLoanInfo(alli);
				in.setApplicantCmInfo(alci);
//				in.setApplicantPbocInfo(alpi);
//				in.getApplicantPbocInfo().getApplicantPbocLoanInfos()
//						.add(alpli);
//				in.getApplicantPbocInfo().getApplicantPbocCcInfos().add(alpci);
				l.add(in);
			}

		} catch (SQLException e) {
			logger.error("查询风险预警待处理客户数据出错 QUERY SQL:" + sql, e);
			e.printStackTrace();
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
		return l;
	}

	/**
	 * 风险预警(按月)批量处理--从 I_INP_BATCH_RISK 表中获得待处理客户数据，供Send使用。
	 * 
	 * @return List
	 * @throws SQLException
	 * @throws Exception
	 */
	public static List<BatchRiskData> getRiskMonthCustInfo(String flagDate)
			throws SQLException {
		init();
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;

		// List<HtmlParseRequest> requestVoList = new
		// ArrayList<HtmlParseRequest>();
		List<BatchRiskData> listRiskData = new ArrayList<BatchRiskData>();
		// 风险预警客户信息(证件号、证件类型、客户姓名)按月
		String sql = "SELECT LOANACNO,CUSTID,PAPERID,PAPERKIND,CUSTNAME,EXCEP_FLAG,"
				+ "CYCLE_ACCT_RATE,CYCLE_ACCT_NUM,CYCLE_CUST_RATE_6,CYCLE_ACCT_NUM_6,CRED_AMT_SUM,CON_CRED_AMT_SUM,"
				+ "LOAN_BAL_SUM,CON_LOAN_BAL_SUM,PRO_TYPE,AUD_SCORE, YEAR_PINCOME, EDUCSIGN, RETUKIND, BEGINDATE FROM I_INP_BATCH_RISK WHERE RECORD_DATE= date'"
				+ flagDate + "'";

		logger.debug("查询客户信息SQL:  " + sql);
		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				/* HtmlParseRequest parseRequest = new HtmlParseRequest();
				 parseRequest.setBANKCODE("12");
				 parseRequest.setTRANDATE("20121212");
				 parseRequest.setTRANTIME("151412369");
				
				 // 证件号码
				 if (rs.getString(1) != null) {
				 parseRequest.setQuerierCertno(rs.getString(1).trim());
				 }
				 // 证件类型
				 if (rs.getString(2) != null) {
				 parseRequest.setQuerierCertype(ChangeCertType.changeCertType(rs.getString(2).trim()));
				 }
				 // 客户姓名
				 if (rs.getString(3) != null) {
				 parseRequest.setQuerierName(rs.getString(3).trim());
				 }
				 requestVoList.add(parseRequest);*/
				
				BatchRiskData rd = new BatchRiskData();
				if (rs.getString(1) != null) {
					rd.setLoanacno(rs.getString(1).trim());
				}
				if (rs.getString(2) != null) {
					rd.setCustid(rs.getString(2).trim());
				}
				if (rs.getString(3) != null) {
					rd.setPaperid(rs.getString(3).trim());
				}
				// 证件类型
				if (rs.getString(4) != null) {
					rd.setPaperkind(ChangeCertType.changeCertType(rs.getString(4).trim()));
				}
				// 客户姓名
				if (rs.getString(5) != null) {
					rd.setCustname(rs.getString(5).trim());
				}
				// 是否例外
				if (rs.getString(6) != null) {
					rd.setExcepFlag(rs.getString(6).trim());
				}
				// 逾期状态账户比率
				if (rs.getBigDecimal(7) != null) {
					rd.setCycleAcctRate(rs.getBigDecimal(7));
				}
				// 逾期笔数
				if (rs.getBigDecimal(8) != null) {
					rd.setCycleAcctNum(rs.getBigDecimal(8));
				}
				// 6月内逾期客户比率
				if (rs.getBigDecimal(9) != null) {
					rd.setCycleCustRate6(rs.getBigDecimal(9));
				}
				// 6月内逾期笔数
				if (rs.getBigDecimal(10) != null) {
					rd.setCycleAcctNum6(rs.getBigDecimal(10));
				}
				// 总授信额度
				if (rs.getBigDecimal(11) != null) {
					rd.setCredAmtSum(rs.getBigDecimal(11));
				}
				// 配偶总授信额度
				if (rs.getBigDecimal(12) != null) {
					rd.setConCredAmtSum(rs.getBigDecimal(12));
				}
				// 总贷款余额
				if (rs.getBigDecimal(13) != null) {
					rd.setLoanBalSum(rs.getBigDecimal(13));
				}
				// 配偶总贷款余额
				if (rs.getBigDecimal(14) != null) {
					rd.setConLoanBalSum(rs.getBigDecimal(14));
				}
				// 货款品种
				if (rs.getString(15) != null) {
					rd.setProType(rs.getString(15).trim());
				}
				// 审批评分结果
				if (rs.getBigDecimal(16) != null) {
					rd.setAudScore(rs.getBigDecimal(16));
				}
				// 年收入
				if (rs.getBigDecimal(17) != null) {
					rd.setYearPincome(rs.getBigDecimal(17));
				}
				// 学历
				if (rs.getString(18) != null) {
					rd.setEducsign(rs.getString(18));
				}
				// 还款方式
				if (rs.getString(19) != null) {
					rd.setRetukind(rs.getString(19));
				}
				
				// 起始日期
				if (rs.getDate(20) != null) {
					rd.setBeginDate(rs.getDate(20));
				}

				listRiskData.add(rd);
			}
		} catch (SQLException e) {
			logger.error("查询风险预警待处理客户数据出错 QUERY SQL:" + sql, e);
			e.printStackTrace();
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
		return listRiskData;
	}

	/**
	 * 风险预警(按月)批量处理--从 BATCH_RISK_DATA 表中获得需要查询人行征信数据的客户信息
	 * 
	 * @return List
	 * @throws SQLException
	 * @throws Exception
	 */
	public static List<HtmlParseRequest> getRiskMonthData(String flagDate)
			throws SQLException {
		init();
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;

		List<HtmlParseRequest> requestVoList = new ArrayList<HtmlParseRequest>();
		// 风险预警客户信息(证件号、证件类型、客户姓名)按月
		String sql = "SELECT distinct PAPERID,PAPERKIND,CUSTNAME FROM BATCH_RISK_DATA WHERE BATCH_NO = '"
				+ flagDate
				+ "' and PROC_FLAG = '1' ";
		logger.debug("查询需要查询人行征信数据的客户信息SQL:  " + sql);
		try {
			connection = connectionProvider.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				HtmlParseRequest parseRequest = new HtmlParseRequest();
				parseRequest.setBANKCODE(BANKCODE);
				parseRequest.setTRANDATE(flagDate);
				SimpleDateFormat df = new SimpleDateFormat("HHmmssSSS");
				parseRequest.setTRANTIME(df.format(new Date()));

				// 证件号码
				if (rs.getString(1) != null) {
					parseRequest.setQuerierCertno(rs.getString(1).trim());
				}
				// 证件类型
				if (rs.getString(2) != null) {
					parseRequest.setQuerierCertype(rs.getString(2).trim());
				}
				// 客户姓名
				if (rs.getString(3) != null) {
					parseRequest.setQuerierName(rs.getString(3).trim());
				}
				requestVoList.add(parseRequest);
			}
		} catch (SQLException e) {
			logger.error("查询需要查询人行征信数据的客户信息出错 QUERY SQL:" + sql, e);
			e.printStackTrace();
			throw e;
		} finally {
			JdbcUtils.closeResultSet(rs);
			JdbcUtils.closeStatement(statement);
			JdbcUtils.closeConnection(connection);
		}
		return requestVoList;
	}

	/**
	 * 向APPLICANTION_INFO(申请信息)表插数据（主表） 向APPLICANT_INFO(申请人信息)表插数据
	 * 向APPLICANT_SPOUSE_INFO(申请人配偶信息)表插数据 向GUARANTOR_INFO(担保人信息)表插数据
	 * 向APPLICANT_LOAN_INFO(申请人贷款信息)表插数据
	 */
	// public static void insertResultInfo(List<IN_PreApproval> ls,
	// String batch_no, String batch_type) throws SQLException {
	// init();
	// Connection connection = connectionProvider.getConnection();
	// Statement statement = connection.createStatement();
	// String sql = "";
	// String sql2 = "";
	// String sql3 = "";
	// String sql4 = "";
	// String sql5 = "";
	// String sql6 = "";
	// String sql7 = "";
	// // String sql8 = "";
	// String sql9 = "";
	// String sql10 = "";
	// String sql11 = "";
	//
	// try {
	// for (int i = 0; i < ls.size(); i++) {
	// IN_PreApproval in = (IN_PreApproval) ls.get(i);
	// ApplicantInfo ali = in.getApplicantInfo();
	// ApplicantSpouseInfo asi = in.getApplicantSpouseInfo();
	// GuarantorInfo gi = in.getGuarantorInfo();
	// ApplicantLoanInfo alli = in.getApplicantLoanInfo();
	// List<OUTTriggerRuleApproval> listout = new
	// ArrayList<OUTTriggerRuleApproval>();
	// listout = in.getoUT_PreparApproval().getTriggerRuleApproval();
	// // 获得主表sequence序列为ID主键,并作为子表的外键
	// long id = getSEQ_APPLICANTION_INFO();
	// sql = "INSERT INTO APPLICANTION_INFO (ID,BATCH_NO,BATCH_TYPE) VALUES ("
	// + id + ",'" + batch_no + "','" + batch_type + "')";
	//
	// sql2 =
	// "INSERT INTO APPLICANT_INFO (ID,APP_ID,CUST_NUMBER,ID_TYPE,ID_NUMBER,APP_CBRC_BC_FLAG,APP_BANK_BC_FLAG) VALUES "
	// + "(SEQ_APPLICANT_INFO.Nextval,"
	// + id
	// + ","
	// + ali.getCustNumber()
	// + ",'"
	// + ali.getIdType()
	// + "','"
	// + ali.getIdNumber()
	// + "','"
	// + ali.getAppCbrcBcFlag()
	// + "','" + ali.getAppBankBcFlag() + "')";
	// sql3 =
	// "INSERT INTO APPLICANT_SPOUSE_INFO (ID,APP_ID,SPO_LOAN_OT,SPO_BANK_BC_FLAG,SPO_CBRC_BC_FLAG) VALUES "
	// + "(SEQ_APPLICANT_SPOUSE.Nextval,"
	// + id
	// + ","
	// + asi.getSpoLoanOt()
	// + ",'"
	// + asi.getSpoBankBcFlag()
	// + "','" + asi.getSpoCbrcBcFlag() + "')";
	// sql4 =
	// "INSERT INTO GUARANTOR_INFO (ID,APP_ID,GRARANTOR_LOAN_OT,GUA_BANK_BC_FLAG,GUA_CBRC_BC_FLAG) VALUES "
	// + "(SEQ_GUARANTOR.Nextval,"
	// + id
	// + ","
	// + gi.getGrarantorLoanOt()
	// + ",'"
	// + gi.getGuaBankBcFlag()
	// + "','"
	// + gi.getGuaCbrcBcFlag()
	// + "')";
	// sql5 =
	// "INSERT INTO APPLICANT_LOAN_INFO (ID,APP_ID,LOAN_ACCOUNT,LOAN_CATEGORY)	VALUES "
	// + "(SEQ_APPLICANT_LOAN.Nextval,"
	// + id
	// + ",'"
	// + alli.getLoanAccount()
	// + "','"
	// + alli.getLoanCategory() + "')";
	//
	// // 组装生成策略调用流水号(流程系统代号+业务模块代号+年+月+日+时+分+秒+ 证件号码)
	// SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
	// String code = "PLD" + sdf.format(System.currentTimeMillis())
	// + ali.getIdNumber();
	//
	// sql6 =
	// "INSERT INTO APPLICANTION_DECISION_RESULT (ID,LOANID,BUSINESS_MODULE,DA_FLOW_ID) VALUES "
	// + "("
	// + id
	// + ","
	// + id
	// + ",'"
	// + in.getoUT_PreparApproval().getBusinessModule()
	// + "','" + code + "')";
	//
	// statement.executeUpdate(sql);
	// statement.executeUpdate(sql2);
	// statement.executeUpdate(sql3);
	// statement.executeUpdate(sql4);
	// statement.executeUpdate(sql5);
	// statement.executeUpdate(sql6);
	// for (int j = 0; j < listout.size(); j++) {
	// OUTTriggerRuleApproval o = new OUTTriggerRuleApproval();
	// o = listout.get(j);
	// long ruleId = getSEQ_TRIGGER_RULE();
	// sql7 =
	// "INSERT INTO TRIGGER_RULE (ID,DECISION_ID,RULESET_CODE,RULESET_NAME,RULE_CODE,RULE_NAME,RULE_PRIORITY,RULE_RESULT_DESC,RULE_RISK_GRADE) VALUES ("
	// + ruleId
	// + ","
	// + id
	// + ",'"
	// + o.getRulesetCode()
	// + "','"
	// + o.getRulesetName()
	// + "','"
	// + o.getRuleCode()
	// + "','"
	// + o.getRuleName()
	// + "',"
	// + o.getRulePriority()
	// + ",'"
	// + o.getRuleResultDesc()
	// + "','"
	// + o.getRuleRiskGrade() + "')";
	// statement.executeUpdate(sql7);
	// // for (int temp = 0; temp < o.getListReason().size(); temp++) {
	// // Reason reason = o.getListReason().get(temp);
	// // if (reason != null) {
	// // sql8 =
	// "INSERT INTO REASON (ID,RULE_ID,REASON_CODE,REASON_DETAIL,REASON_TYPE"
	// // + " VALUES"
	// // + " SEQ_REASON.Nextval,"
	// // + ruleId
	// // + ",'"
	// // + reason.getReasonCode()
	// // + "','"
	// // + reason.getReasonDetail()
	// // + "','"
	// // + reason.getReasonType() + "')";
	// // statement.executeUpdate(sql8);
	// // }
	// // }
	// }
	// for (int y = 0; y < in.getoUT_PreparApproval()
	// .getListDecFlowinfo().size(); y++) {
	// DecisionFlowItemInfo desc = in.getoUT_PreparApproval()
	// .getListDecFlowinfo().get(y);
	// Long descId = getSEQ_D_FLOW_ITEM_INFO();
	// sql9 =
	// "INSERT INTO DECISION_FLOW_ITEM_INFO (ID,DECISION_ID,RULE_FLOW_NUMBER,RULE_FLOW_NAME) VALUES"
	// + "("
	// + descId
	// + ","
	// + id
	// + ",'"
	// + desc.getRuleFlowNumber()
	// + "','"
	// + desc.getRuleFlowName() + "')";
	// statement.executeUpdate(sql9);
	// Iterator iter = desc.getDecisionComponentItemInfos().iterator();
	// while(iter.hasNext()){
	// DecisionComponentItemInfo dci = (DecisionComponentItemInfo)iter.next();
	// if (dci != null) {
	// long dciId = getSEQ_D_COMPONENT_ITEM_INFO();
	// sql10 =
	// "INSERT INTO DECISION_COMPONENT_ITEM_INFO (ID,FLOW_ID,COMPONENT_NUMBER,COMPONENT_NAME,COMPONENT_TYPE) VALUES"
	// + "("
	// + dciId
	// + ","
	// + descId
	// + ",'"
	// + dci.getComponentNumber()
	// + "','"
	// + dci.getComponentName()
	// + "','"
	// + dci.getComponentType() + "')";
	// statement.executeUpdate(sql10);
	// Iterator iterator = dci.getRuleItemInfos().iterator();
	// while(iterator.hasNext()){
	// RuleItemInfo rii = (RuleItemInfo)iterator.next();
	// if (rii != null) {
	// sql11 =
	// "INSERT INTO RULE_ITEM_INFO (ID,COMPONENT_ID,RULE_NO,RULE_NAME)VALUES"
	// + "(SEQ_RULE_ITEM_INFO.Nextval,"
	// + dciId
	// + ",'"
	// + rii.getRuleNo()
	// + "','" + rii.getRuleName() + "')";
	// statement.executeUpdate(sql11);
	// }
	// }
	// }
	// }
	// }
	//
	// }
	// } catch (SQLException e) {
	// connection.rollback();
	// e.printStackTrace();
	// logger.error("新增风险预警处理结果数据时出错", e);
	// logger.error("insert sql: " + sql);
	// logger.error("insert sql2: " + sql2);
	// logger.error("insert sql3: " + sql3);
	// logger.error("insert sql4: " + sql4);
	// logger.error("insert sql5: " + sql5);
	// logger.error("insert sql6: " + sql6);
	// logger.error("insert sql7: " + sql7);
	// // logger.error("insert sql8: " + sql8);
	// logger.error("insert sql9: " + sql9);
	// logger.error("insert sql10: " + sql10);
	// logger.error("insert sql11: " + sql11);
	// throw e;
	// } finally {
	// JdbcUtils.closeStatement(statement);
	// JdbcUtils.closeConnection(connection);
	// }
	//
	// }

	/**
	 * 获得主表(DECISION_COMPONENT_ITEM_INFO)的sequence序列为ID主键,并作为子表的外键
	 * 
	 * @return Long
	 * @throws Exception
	 */
	// public static long getSEQ_D_COMPONENT_ITEM_INFO() throws SQLException {
	// init();
	// Connection connection = null;
	// Statement statement = null;
	// ResultSet rs = null;
	// long lId = 0;
	// String sql = "SELECT SEQ_D_COMPONENT_ITEM_INFO.nextval FROM DUAL";
	// try {
	// connection = connectionProvider.getConnection();
	// statement = connection.createStatement();
	// rs = statement.executeQuery(sql);
	// while (rs.next()) {
	// lId = rs.getLong(1);
	// }
	//
	// } catch (SQLException e) {
	// logger.error("获得主表(DECISION_COMPONENT_ITEM_INFO)的sequence序列时出错:"
	// + sql, e);
	// e.printStackTrace();
	// throw e;
	// } finally {
	// JdbcUtils.closeResultSet(rs);
	// JdbcUtils.closeStatement(statement);
	// JdbcUtils.closeConnection(connection);
	// }
	// return lId;
	// }

	/**
	 * 获得主表(APPLICANTION_INFO)的sequence序列为ID主键,并作为子表的外键
	 * 
	 * @return Long
	 * @throws Exception
	 */
	// public static long getSEQ_APPLICANTION_INFO() throws SQLException {
	// init();
	// Connection connection = null;
	// Statement statement = null;
	// ResultSet rs = null;
	// long lId = 0;
	// String sql = "SELECT SEQ_APPLICANTION_INFO.nextval FROM DUAL";
	// try {
	// connection = connectionProvider.getConnection();
	// statement = connection.createStatement();
	// rs = statement.executeQuery(sql);
	// while (rs.next()) {
	// lId = rs.getLong(1);
	// }
	//
	// } catch (SQLException e) {
	// logger.error("获得主表(APPLICANTION_INFO)的sequence序列时出错:" + sql, e);
	// e.printStackTrace();
	// throw e;
	// } finally {
	// JdbcUtils.closeResultSet(rs);
	// JdbcUtils.closeStatement(statement);
	// JdbcUtils.closeConnection(connection);
	// }
	// return lId;
	// }

	/**
	 * 获得主表(DECISION_FLOW_ITEM_INFO)的sequence序列为ID主键,并作为子表的外键
	 * 
	 * @return Long
	 * @throws Exception
	 */
	// public static long getSEQ_D_FLOW_ITEM_INFO() throws SQLException {
	// init();
	// Connection connection = null;
	// Statement statement = null;
	// ResultSet rs = null;
	// long lId = 0;
	// String sql = "SELECT SEQ_D_FLOW_ITEM_INFO.nextval FROM DUAL";
	// try {
	// connection = connectionProvider.getConnection();
	// statement = connection.createStatement();
	// rs = statement.executeQuery(sql);
	// while (rs.next()) {
	// lId = rs.getLong(1);
	// }
	//
	// } catch (SQLException e) {
	// logger.error("获得主表(DECISION_FLOW_ITEM_INFO)的sequence序列时出错:" + sql,
	// e);
	// e.printStackTrace();
	// throw e;
	// } finally {
	// JdbcUtils.closeResultSet(rs);
	// JdbcUtils.closeStatement(statement);
	// JdbcUtils.closeConnection(connection);
	// }
	// return lId;
	// }

	/**
	 * 获得主表(APPLICANTION_INFO)的sequence序列为ID主键,并作为子表的外键
	 * 
	 * @return Long
	 * @throws Exception
	 */
	// public static long getSEQ_TRIGGER_RULE() throws SQLException {
	// init();
	// Connection connection = null;
	// Statement statement = null;
	// ResultSet rs = null;
	// long lId = 0;
	// String sql = "SELECT SEQ_TRIGGER_RULE.nextval FROM DUAL";
	// try {
	// connection = connectionProvider.getConnection();
	// statement = connection.createStatement();
	// rs = statement.executeQuery(sql);
	// while (rs.next()) {
	// lId = rs.getLong(1);
	// }
	//
	// } catch (SQLException e) {
	// logger.error("获得主表(TRIGGER_RULE)的sequence序列时出错:" + sql, e);
	// e.printStackTrace();
	// throw e;
	// } finally {
	// JdbcUtils.closeResultSet(rs);
	// JdbcUtils.closeStatement(statement);
	// JdbcUtils.closeConnection(connection);
	// }
	// return lId;
	// }

	/**
	 * 删除APPLICANTION_INFO(申请信息)表数据（主表） 删除APPLICANT_INFO(申请人信息)表数据
	 * 删除APPLICANT_SPOUSE_INFO(申请人配偶信息)表数据 删除GUARANTOR_INFO(担保人信息)表数据
	 * 删除APPLICANT_LOAN_INFO(申请人贷款信息)表数据
	 */
	// public static void deleteReslutInfoByid(List<String> idList)
	// throws SQLException {
	// init();
	// Connection connection = null;
	// Statement statement = null;
	// // 删除触发规则信息
	// String triggerRuleDel =
	// "DELETE FROM  TRIGGER_RULE WHERE DECISION_ID = ?";
	// // 删除申请决策结果信息
	// String decisionResultDel =
	// "DELETE FROM APPLICANTION_DECISION_RESULT WHERE LOANID =?";
	// // 删除GUARANTOR_INFO(担保人信息)表数据
	// String guarantorDel = "DELETE FROM GUARANTOR_INFO WHERE APP_ID =?";
	// // 删除APPLICANT_SPOUSE_INFO(申请人配偶信息)
	// String spouseDel = "DELETE FROM APPLICANT_SPOUSE_INFO  WHERE APP_ID = ?";
	// // 删除APPLICANT_LOAN_INFO(申请人贷款信息)表数据
	// String loanDel = "DELETE FROM APPLICANT_LOAN_INFO WHERE  APP_ID =?";
	// // 删除APPLICANT_INFO(申请人信息)表数据
	// String applicantDel = "DELETE FROM APPLICANT_INFO WHERE APP_ID = ?";
	// // 删除APPLICANTION_INFO(申请信息)表数据（主表）
	// String applicantionDel = "DELETE FROM APPLICANTION_INFO WHERE ID =? ";
	// try {
	// connection = connectionProvider.getConnection();
	// PreparedStatement ps = connection.prepareStatement(triggerRuleDel);
	// PreparedStatement ps2 = connection
	// .prepareStatement(decisionResultDel);
	// PreparedStatement ps3 = connection.prepareStatement(guarantorDel);
	// PreparedStatement ps4 = connection.prepareStatement(spouseDel);
	// PreparedStatement ps5 = connection.prepareStatement(loanDel);
	// PreparedStatement ps6 = connection.prepareStatement(applicantDel);
	// PreparedStatement ps7 = connection
	// .prepareStatement(applicantionDel);
	//
	// for (int i = 0; i < idList.size(); i++) {
	// String id = (String) idList.get(i);
	// ps.setString(1, id);
	// ps.execute();
	// ps2.setString(1, id);
	// ps2.execute();
	// ps3.setString(1, id);
	// ps3.execute();
	// ps4.setString(1, id);
	// ps4.execute();
	// ps5.setString(1, id);
	// ps5.execute();
	// ps6.setString(1, id);
	// ps6.execute();
	// ps7.setString(1, id);
	// ps7.execute();
	// }
	// } catch (SQLException e) {
	// connection.rollback();
	// logger.error("删除风险预警已处理数据时出错", e);
	// e.printStackTrace();
	// throw e;
	// } finally {
	// JdbcUtils.closeStatement(statement);
	// JdbcUtils.closeConnection(connection);
	// }
	// }

	/**
	 * 根据批次日期、批次类型查询APPLICANTION_INFO(申请信息)表数据（主表） 获得主表ID，删除子表及主表批处理结果数据
	 * 
	 * @param batch_no
	 * @param batch_type
	 */
	// public static List<String> delForBatchNo(String batch_no, String
	// batch_type)
	// throws SQLException {
	// init();
	// Connection connection = null;
	// Statement statement = null;
	// ResultSet rs = null;
	// List<String> bList = new ArrayList<String>();
	// String sql = "SELECT ID FROM APPLICANTION_INFO WHERE BATCH_NO = '"
	// + batch_no + "' AND BATCH_TYPE = '" + batch_type + "'";
	// try {
	// connection = connectionProvider.getConnection();
	// statement = connection.createStatement();
	// rs = statement.executeQuery(sql);
	// while (rs.next()) {
	// String id = String.valueOf(rs.getInt(1));
	// bList.add(id);
	// }
	// } catch (SQLException e) {
	// logger.error("查询APPLICANTION_INFO(申请信息)表数据（主表） 获得主表ID是出错: " + sql,
	// e);
	// e.printStackTrace();
	// throw e;
	// } finally {
	// JdbcUtils.closeResultSet(rs);
	// JdbcUtils.closeStatement(statement);
	// JdbcUtils.closeConnection(connection);
	// }
	// return bList;
	// }
}
